package com.gopay.common.manager.ptbank.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.gopay.common.dao.ptbank.GsrtBankHfTdAcctDtlDao;
import com.gopay.common.domain.ptbank.GsrtBankHfTdAcctDtl;
import com.gopay.common.manager.ptbank.GsrtBankHfTdAcctDtlManager;
import com.gopay.common.order.dao.CommonQueryDAO;

/**
 * 标的放款明细文件 ManagerImpl
 * GsrtBankHfTdAcctDtlManagerImpl.java
 * 2016年4月27日
 * @author donghuijun
 *  www.gopay.com.cn Inc.All rights reserved.
 */
@Service("gsrtBankHfTdAcctDtlManager")
public class GsrtBankHfTdAcctDtlManagerImpl implements GsrtBankHfTdAcctDtlManager {
    @Resource(name = "gsrtBankHfTdAcctDtlDao")
    private GsrtBankHfTdAcctDtlDao gsrtBankHfTdAcctDtlDao;
    @Resource(name = "commonQueryDAO")
    private CommonQueryDAO commonQueryDAO;
    
    
	@Override
	public List<GsrtBankHfTdAcctDtl> getDtlListByDate(String date) {
		List<Object[]> list = commonQueryDAO.findBySql(getTdAcctDtlSql(date));
		List<GsrtBankHfTdAcctDtl> sumList= new ArrayList<GsrtBankHfTdAcctDtl>();
		for (Object[] object : list) {
			GsrtBankHfTdAcctDtl dtl = new GsrtBankHfTdAcctDtl();
			dtl.setProid(object[0]==null ? "" : object[0].toString());
			dtl.setTransdt(object[1]==null ? "" : object[1].toString());
			dtl.setTranstype(object[2]==null ? "" : object[2].toString());
			dtl.setName(object[3]==null ? "" : object[3].toString());
			dtl.setCerttype(object[4]==null ? "" : object[4].toString());
			dtl.setCertnumber(object[5]==null ? "" : object[5].toString());
			dtl.setBorrcustid(object[6]==null ? "" : object[6].toString());
			dtl.setBorrname(object[7]==null ? "" : object[7].toString());
			dtl.setBorrcerttype(object[8]==null ? "" : object[8].toString());
			dtl.setBorrcertid(object[9] == null ? "0.00" : object[9].toString());
			dtl.setCreditmoney(new BigDecimal( object[10] == null ? "0.00" : object[10].toString()).setScale(2));
			dtl.setRepayprcp(new BigDecimal(object[11] == null ? "0.00" : object[11].toString()).setScale(2));
			dtl.setRepayintst(new BigDecimal(object[12] == null ? "0.00" : object[12].toString()).setScale(2));
		
			sumList.add(dtl);
		}
		return sumList;
	}
	
	private String getTdAcctDtlSql(String date){
		StringBuffer sql = new StringBuffer();
		sql.append("select a.gopay_order_id,a.gopay_txn_tm,cast(a.trade_type as varchar(4))as trade_type,a.investe_name,a.investe_cert_type,")
		   .append("a.investe_cert_no,a.cust_id,a.cust_name,a.cert_type,a.cert_no,a.loan_amt,a.return_amt,a.interest ")
		   .append("from (select i.gopay_order_id,i.gopay_txn_tm,")
		   		 .append("'还款' as trade_type,")
		   		 .append("case when pi.cust_id is not null then pi.cust_name else ei.corp_name end as investe_name,")
		   		 .append("case when pi.cust_id is not null then pi.cert_type else ei.legal_person_cert_type end as investe_cert_type,")
		   		 .append("case when pi.cust_id is not null then pi.cert_no else ei.legal_person_cert_no end as investe_cert_no,")
		   		 .append("case when p.cust_id is not null then p.cust_id else e.cust_id end as cust_id,")
		   		 .append("case when p.cust_id is not null then p.cust_name else e.corp_name end as cust_name,")
		   		 .append("case when p.cust_id is not null then p.cert_type else e.legal_person_cert_type end as cert_type,")
		   		 .append("case when p.cust_id is not null then p.cert_no else e.legal_person_cert_no end as cert_no,")
		   		 .append("0.00 as loan_amt,")
		   		 .append("rs.mer_txn_amt as return_amt,")
		   		 .append("0.00 as interest ") 
		   		 .append("from gsrt_bids_info i ")
		   		 .append("inner join GSRT_CUST_SIGNED c on c.CONSTRACTER_ID=i.borrower_constracter_id ")
		   		 .append("left join cust_person_info p on p.cust_id=c.cust_id ")
		   		 .append("left join cust_corp_info e on e.cust_id=c.cust_id ")
		   		 .append("inner join GSRT_RETURNED_MONEY_MASTER r on (r.MER_CUST_ID=i.mer_cust_id and r.bid_no=i.bid_no) ")
		   		 .append("left join GSRT_RETURNED_MONEY_MSG_SUB rs on rs.returned_money_id=r.id ")
		   		 .append("left join cust_person_info pi on pi.cust_id=rs.main_recv_cust_id ")
		   		 .append("left join cust_corp_info ei on ei.cust_id=rs.main_recv_cust_id ")
		   		 .append("where r.txn_sta_cd=20000 and rs.txn_sta_cd=20000 and to_char(i.create_ts,'YYYYMMDD')='").append(date).append("' ")
		   		 .append("order by i.gopay_order_id ")
		   	.append(") a ")
          .append("union all ")
          .append("select b.gopay_order_id,b.gopay_txn_tm,cast(b.trade_type as varchar(4))as trade_type,b.investe_name,b.investe_cert_type,")
          .append("b.investe_cert_no,b.cust_id,b.cust_name,b.cert_type,b.cert_no,b.loan_amt,b.return_amt,b.interest ")
          .append("from (select i.gopay_order_id,i.gopay_txn_tm,")
          	     .append("'放款' as trade_type,")
          	     .append("'' as investe_name,")
          	     .append("'' as investe_cert_type,")
          		 .append("'' as investe_cert_no,")
          		 .append("case when p.cust_id is not null then p.cust_id else e.cust_id end as cust_id,")
          		 .append("case when p.cust_id is not null then p.cust_name else e.corp_name end as cust_name,")
          		 .append("case when p.cust_id is not null then p.cert_type else e.legal_person_cert_type end as cert_type,")
          		 .append("case when p.cust_id is not null then p.cert_no else e.legal_person_cert_no end as cert_no,")
          		 .append("l.mer_txn_amt as loan_amt,")
          		 .append("0.00 as return_amt,")
          		 .append("0.00 as interest ")
	             .append("from gsrt_bids_info i ")
	             .append("inner join GSRT_CUST_SIGNED c on c.CONSTRACTER_ID=i.borrower_constracter_id ")
	             .append("left join cust_person_info p on p.cust_id=c.cust_id ")
	             .append("left join cust_corp_info e on e.cust_id=c.cust_id ")
	             .append("inner join GSRT_LOAN_DISBURSEMENTS_MASTER l on l.constracter_id=i.borrower_constracter_id ")
	             .append("where l.txn_sta_cd=20000 and to_char(i.create_ts,'YYYYMMDD')='").append(date).append("' ")
	             .append("order by i.gopay_order_id")
	      .append(") b");
		
		
		return sql.toString();
	}
}
